﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Reflection;
using System.Linq.Expressions;
using System.Diagnostics;
using ORMAC.Reflection;
using System.Reflection.Emit;
using System.Threading;

namespace ORMAC.Core
{
    internal class DatabaseLoader:LoaderBase
    {
        /// <summary>
        /// keeps a list of context members for each object type
        /// </summary>
        private ContextMemberCollection context;
        /// <summary>
        /// Used internally to avoid circular reference to objects
        /// </summary>        
        private Dictionary<Type,Dictionary<string, object>> cache=new Dictionary<Type,Dictionary<string,object>>();
        public DatabaseLoader(ContextMemberCollection context)
        {
            this.context = context;
        }
        public DatabaseLoader()
        {
            this.context = new ContextMemberCollection();
        }
        /// <summary>
        /// creates the main query
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="connectionString"></param>
        /// <param name="schemaName"></param>
        /// <param name="query"></param>
        /// <returns></returns>
        public DataSet PrefetchDataFromDB<T>(string connectionString, string schemaName,Expression<Func<T,bool>> query)
        {
#if DEBUG
            Stopwatch s = new Stopwatch();
            s.Start();
#endif
            allTables = ReflectionHelper.FindAllTables<T>(schemaName,context);
            string joinSQL=BulidJoinSQL<T>(allTables);
            string colsSQL = BulidSelectSQL<T>(allTables);
            string whereSQL = BulidWhereSQL<T>(allTables,query);
            string mainQuery=colsSQL+joinSQL+whereSQL;
            var res=InternalPrefetchDataFromDB<T>(connectionString, schemaName,mainQuery);
#if DEBUG
            s.Stop();
            //Console.WriteLine("ORMAC prefetch time:"+s.ElapsedMilliseconds);
#endif

            return res;
        }
        public DataSet PrefetchDataFromDB<T>(string connectionString, string schemaName, string query=null)
        {
#if DEBUG
            Stopwatch s = new Stopwatch();
            s.Start();
#endif
            allTables = ReflectionHelper.FindAllTables<T>(schemaName, context);
            string joinSQL = BulidJoinSQL<T>(allTables);
            string colsSQL = BulidSelectSQL<T>(allTables);
            string whereSQL = query;
            string mainQuery = colsSQL + joinSQL + whereSQL;
            var res = InternalPrefetchDataFromDB<T>(connectionString, schemaName, mainQuery);
#if DEBUG
            s.Stop();
            //Console.WriteLine("ORMAC prefetch time:" + s.ElapsedMilliseconds);
#endif
            return res;
        }
        /// <summary>
        /// Builds the where clause of the query
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="tables"></param>
        /// <returns></returns>
        private string BulidWhereSQL<T>(IEnumerable<TableInfo> tables, Expression<Func<T, bool>> query)
        {
            StringBuilder sb = new StringBuilder();   
            LinqParser parser = new LinqParser();
            TableInfo main = tables.First<TableInfo>(t => t.ClassType == typeof(T));
            //if (!string.IsNullOrEmpty(main.Query))
            //    return main.Query;
            //else 
            string whereClause = parser.ParseSQLQuery<T>(query, tables);
            if (!string.IsNullOrEmpty(whereClause))
            {
                sb.Append(" where ");
                sb.Append(whereClause);
            }
            return sb.ToString();
        }
        /// <summary>
        /// Builds aa list of fields to be retrieved
        /// </summary>
        /// <typeparam name="T1"></typeparam>
        /// <param name="iEnumerable"></param>
        /// <returns></returns>
        private string BulidSelectSQL<T>(IEnumerable<TableInfo> tables)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select ");
            int i = 0;
            foreach (TableInfo table in tables)
            {
                foreach (ColumnInfo col in table.Columns)
                {
                    if(i>0)
                        sb.Append(",");
                    //sb.Append("[");
                    //sb.Append(table.Name);
                    //sb.Append("]");
                    sb.Append(GetFullTableName(table.Name));
                    sb.Append(".");
                    sb.Append(col.DBName);
                    sb.Append(" as ");
                    sb.Append("[");
                    sb.Append(col.DatasetName);
                    sb.Append("]");
                    i++;
                }
            }
            return sb.ToString();

        }
        private string GetFullTableName(string tableName){
            if(string.IsNullOrEmpty(tableName))
                return tableName;
            List<string> nameParts=tableName.Split('.').ToList<string>();
            StringBuilder sb=new StringBuilder();
            int i = 0;
            foreach(var part in nameParts){
                if (i > 0)
                    sb.Append(".");
                sb.Append("[");
                sb.Append(part);
                sb.Append("]");
                i++;
            }
            return sb.ToString();
        }
        /// <summary>
        /// Build the join claause of the select statement
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="tables"></param>
        /// <returns></returns>
        private string BulidJoinSQL<T>(IEnumerable<TableInfo> tables)
        {
            StringBuilder sb=new StringBuilder();
            TableInfo main=tables.First<TableInfo>(t=>t.ClassType==typeof(T));
            sb.Append(" from ");
            if (string.IsNullOrEmpty(main.Query))
            {
                /*sb.Append("[");
                sb.Append(main.Name);
                sb.Append("]");*/
                sb.Append(GetFullTableName(main.Name));
            }
            else
            {
                sb.Append("(");
                sb.Append(main.Query);
                sb.Append(") ");
                sb.Append("as ");
                sb.Append(main.Name);
            }

            foreach (TableInfo table in tables)
            {
                //Skip join for the first table
                if (table.Name == main.Name)
                    continue;
                if (table.JoinInfo != null && table.Query==null)
                {
                    sb.Append(" left outer join ");
                    //sb.Append("[");
                    //sb.Append(table.Name);
                    //sb.Append("]");
                    sb.Append(GetFullTableName(table.Name));
                    sb.Append(" on ");
                    //sb.Append("[");
                    //sb.Append(table.JoinInfo.ParnetTable);
                    //sb.Append("]");
                    sb.Append(GetFullTableName(table.JoinInfo.ParnetTable));
                    sb.Append(".");
                    sb.Append(table.JoinInfo.ParnetCol);
                    sb.Append("=");
                    //sb.Append("[");
                    //sb.Append(table.Name);
                    //sb.Append("]");
                    sb.Append(GetFullTableName(table.Name));
                    sb.Append(".");
                    sb.Append(table.JoinInfo.ChildCol);
                }
                if (table.LinkInfo != null && table.Query==null)
                {
                    sb.Append(" left outer join ");
                    //sb.Append("[");
                    //sb.Append(table.LinkInfo.LinkTableName);
                    //sb.Append("]");
                    sb.Append(GetFullTableName(table.LinkInfo.LinkTableName));

                    sb.Append(" on ");
                    //sb.Append("[");
                    //sb.Append(table.LinkInfo.LinkTableName);
                    //sb.Append("]");
                    sb.Append(GetFullTableName(table.LinkInfo.LinkTableName));

                    sb.Append(".");
                    sb.Append(table.LinkInfo.LinkTableParnetCol);
                    sb.Append("=");
                    //sb.Append("[");
                    //sb.Append(table.LinkInfo.ParnetTableName);
                    //sb.Append("]");
                    sb.Append(GetFullTableName(table.LinkInfo.ParnetTableName));

                    sb.Append(".");
                    sb.Append(table.LinkInfo.ParnetCol);

                    sb.Append(" left outer join ");
                    //sb.Append("[");
                    //sb.Append(table.Name);
                    //sb.Append("]");
                    sb.Append(GetFullTableName(table.Name));

                    sb.Append(" on ");
                    //sb.Append("[");
                    //sb.Append(table.LinkInfo.LinkTableName);
                    //sb.Append("]");
                    sb.Append(GetFullTableName(table.LinkInfo.LinkTableName));

                    sb.Append(".");
                    sb.Append(table.LinkInfo.LinkTableChildCol);
                    sb.Append("=");
                    //sb.Append("[");
                    //sb.Append(table.Name);
                    //sb.Append("]");
                    sb.Append(GetFullTableName(table.Name));

                    sb.Append(".");
                    sb.Append(table.LinkInfo.ChildCol);

                }
                if (table.Query != null)
                {
                    sb.Append(" left outer join (");
                    sb.Append(table.Query);
                    sb.Append(")");
                    sb.Append(" as ");
                    sb.Append(table.Name);
                    sb.Append(" on ");
                    sb.Append(table.JoinInfo.ParnetTable);
                    sb.Append(".");
                    sb.Append(table.JoinInfo.ParnetCol);
                    sb.Append("=");
                    //sb.Append("[");
                    //sb.Append(table.Name);
                    //sb.Append("]");
                    sb.Append(GetFullTableName(table.Name));
                    sb.Append(".");
                    sb.Append(table.JoinInfo.ChildCol);
                }


            }
            return sb.ToString();
        }
        /// <summary>
        /// recursive method to fetch data from database
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="connectionString"></param>
        /// <param name="schemaName"></param>
        /// <param name="query"></param>
        /// <returns></returns>
        private DataSet InternalPrefetchDataFromDB<T>(string connectionString, string schemaName,string query)
        {
            try
            {
                mainTableName = ReflectionHelper.TableName<T>(schemaName);

                DataSet res = new DataSet();
#if DEBUG
                Stopwatch s = new Stopwatch();
                s.Start();
#endif

                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    //Create connection to the DB
                    using (SqlCommand searchCommand = new SqlCommand())
                    {
                        searchCommand.CommandType = CommandType.Text;
                        searchCommand.CommandText =query;
                        searchCommand.Connection = connection;
                        //Create Data Adapter
                        using (SqlDataAdapter dataAdapter = new SqlDataAdapter(searchCommand))
                        {
                            //Fill the data Table with the result
                            DataTable dt = new DataTable(mainTableName);
                            dataAdapter.Fill(dt);
                            res.Tables.Add(dt);
                        }
                    }
                }
#if DEBUG
                s.Stop();
                //Console.WriteLine("ORMAC prefetch data access time:"+s.ElapsedMilliseconds);
#endif
                return res;
            }
            catch
            {
                //TODO: throw proper exceptions
                throw;
            }
        }
        /// <summary>
        /// Core method to convert a database row to object
        /// </summary>
        /// <typeparam name="T">Object Type</typeparam>
        /// <param name="schema">DB schema name</param>
        /// <param name="item">Datatable row</param>
        /// <param name="tableName">dataset table name</param>
        /// <param name="prefix">Prefix for the table name</param>
        /// <param name="onlyValueObjects">single level conversion</param>
        /// <returns>Object Model</returns>
        protected override T ConvertItem2Object<T>(string schema, DataRow item, string tableName, string prefix = null, bool onlyValueObjects = false)            
        {
            TableInfo currTable = allTables.First<TableInfo>(t => t.Name == tableName);
            //Create the class
            var data = new T();
            //if there iss nothing to convert then return a null object
            if (IsNull(currTable,item))
                return data;
            //don't convert the item if we have that in cache
            string hash = item.GetRowHashCode<T>(tableName);
            lock (cache)
            {
                if (cache.ContainsKey(typeof(T)) && cache[typeof(T)].ContainsKey(hash))
                    return (T)cache[typeof(T)][hash];
                if (!cache.ContainsKey(typeof(T)))
                    cache.Add(typeof(T), new Dictionary<string, object>());
                //add this object to the cache
                cache[typeof(T)].Add(hash, data);
            }
            
            //start converting class properties
            var properties=ReflectionCacheManager.GetProperties(typeof(T));
            foreach (var propertyInfo in properties)
            {
                //Skip this one if not mapped or explicitly said not to load
                if (!ReflectionHelper.IsMappedAndInContext<T>(propertyInfo, schema, context) || !ReflectionHelper.IsAutoLoad(propertyInfo,schema))
                    continue;                
                #region Find All attributes of this property
                string propertyTableName = null;
                //Get Property table name
                if (!propertyInfo.PropertyType.IsValueType && propertyInfo.PropertyType!=typeof(string))
                {
                    if (!propertyInfo.PropertyType.IsGenericType  )
                        propertyTableName = ReflectionHelper.TableName(propertyInfo.PropertyType, schema);
                    else
                    {
                        Type internalType = propertyInfo.PropertyType.GetGenericArguments()[0];
                        propertyTableName = ReflectionHelper.TableName(internalType, schema);
                    }
                }
                //Get the property column name
                string propertyFieldName = ReflectionHelper.FieldName<T>(schema, propertyInfo.Name);
                //for each property, the name of the columns are prefixed with the actual property table name in the database
                propertyFieldName =!string.IsNullOrEmpty(propertyTableName)?propertyTableName:tableName + "_" + propertyFieldName;
                string fieldPrefix = ReflectionHelper.Prefix<T>(schema, propertyInfo.Name);
                JoinColumns join = ReflectionHelper.GetJoinInfo<T>(schema, propertyInfo.Name);
                if (join != null)
                {
                    join.ChildCol = propertyTableName + "_" + join.ChildCol;
                    join.ParnetCol = tableName + "_" + join.ParnetCol;
                }
                LinkTables link = ReflectionHelper.GetLinkInfo<T>(schema, propertyInfo.Name);
                if (link != null)
                {
                    link.ChildCol = propertyTableName + "_" + link.ChildCol;
                    link.ParnetCol = tableName + "_" + link.ParnetCol;
                }
                #endregion
                //don't convert the properties which don't have mappings defined
                if (string.IsNullOrEmpty(propertyTableName) && string.IsNullOrEmpty(propertyFieldName))
                    continue;
                //Handle Generic property types
                if (propertyInfo.PropertyType.IsGenericType  && !onlyValueObjects)
                {
                    Type internalType = propertyInfo.PropertyType.GetGenericArguments()[0];
                    string tCriteria = null;
                    //This is a simple join
                    if (link == null)
                    {
                        if (!FindTable(mainTableName) || !dataSet.Tables[mainTableName].Columns.Contains(join.ChildCol))
                            continue;
                        object parentValue = item.GetObject(join.ParnetCol);
                        tCriteria = parentValue is string ? join.ChildCol + "='" + parentValue + "'" : join.ParnetCol + "=" + parentValue;
                    }
                    //join using a join table
                    else
                    {
                        if (!FindTable(mainTableName) || !dataSet.Tables[mainTableName].Columns.Contains(link.ChildCol))
                            continue;
                        object parentValue = item.GetObject(link.ParnetCol);
                        tCriteria = parentValue is string ? link.ChildCol + "='" + parentValue + "'" : link.ParnetCol + "=" + parentValue;

                        //tCriteria = BuildLinkCriteria(propertyInfo.PropertyType, item,link);
                    }
                     
                    MethodInfo methodInfo = this.GetType().GetMethod("GetListWithTableName",BindingFlags.Instance|BindingFlags.NonPublic);
                    MethodInfo method = methodInfo.MakeGenericMethod(new Type[] { internalType });
                    object value = method.Invoke(this, new object[] { dataSet, schema, propertyTableName, tCriteria,onlyValueObjects });
                    propertyInfo.SetValue(data, value, null);
                }
                //Non Generic
                else
                {
                    DataRow childItem = null;
                    //If this property comes from another table then get the proper row of that table
                    if (!string.IsNullOrEmpty(propertyTableName) && propertyTableName != tableName && !string.IsNullOrEmpty(tableName) && !onlyValueObjects)
                    {
                        object parentValue=item.GetObject(join.ParnetCol);
                        childItem = FindItem(mainTableName, join.ChildCol,parentValue);
                        //if (childItem == null)
                        //    continue;
                    }
                    //if the property is a class then convert the class recursively
                    if (propertyInfo.PropertyType.IsClass && !IsBlobOrString(propertyInfo.PropertyType) )
                    {
                        if (!onlyValueObjects)
                        {

                            MethodInfo methodInfo = this.GetType().GetMethod("ConvertItem2Object",BindingFlags.Instance|BindingFlags.NonPublic);
                            MethodInfo method = methodInfo.MakeGenericMethod(new Type[] { propertyInfo.PropertyType });
                            object value = method.Invoke(this, new object[] { schema, childItem ?? item, propertyTableName, fieldPrefix, onlyValueObjects });
                            propertyInfo.SetValue(data, value, null);
                        }
                    }
                    else//if this is a simple property then convert it
                    {
                        var value=childItem == null ? item.GetValue(propertyFieldName, propertyInfo.PropertyType, schema) : childItem.GetValue(propertyFieldName, propertyInfo.PropertyType, schema);
                        propertyInfo.SetValue(data,value,null);
                    }
                }
            }
            return data;

        }
        private bool IsBlobOrString(Type type)
        {
            if (type == typeof(string) || type == typeof(Byte[]))
                return true;
            else
                return false;
        }
        /// <summary>
        /// builds selection criteria for link tables
        /// </summary>
        /// <param name="item"></param>
        /// <param name="link"></param>
        /// <returns></returns>
        private string BuildLinkCriteria(Type propertyType, DataRow item, LinkTables link)
        {
            IEnumerable<string> primaryKeys = ReflectionHelper.GetPrimaryKeys(propertyType, "", link.LinkTableName);
            object parentValue = item.GetObject(link.ParnetCol);
            string tCriteria=null;
            tCriteria =parentValue is string? link.LinkTableParnetCol + "='" + parentValue + "'":link.LinkTableParnetCol + "=" + parentValue;
            List<string> criterias = new List<string>();
            var items = FindItems(link.LinkTableName, tCriteria,primaryKeys);
            foreach (var it in items)
            {
                object linkValue = it.GetObject(link.LinkTableChildCol);
                if (!(linkValue is DBNull))
                {
                    tCriteria = linkValue is string ? link.ChildCol + "='" + linkValue + "'" : link.ChildCol + "=" + linkValue;
                    criterias.Add(tCriteria);
                }
            }
            string res = string.Empty;
            if (criterias.Count > 0)
            {
                res = criterias[0];
                for (int i=1;i<criterias.Count;i++)
                {
                    res += " or " + criterias[i];
                }
            }
            return res;
        }
        /// <summary>
        /// finds an item in specified table using row id
        /// </summary>
        /// <param name="tableName">Table Name</param>
        /// <param name="idColName">id coloumn name</param>
        /// <param name="id">row id</param>
        /// <returns></returns>
        protected virtual DataRow FindItem(string tableName, string idColName, object id)
        {
            if (string.IsNullOrEmpty(tableName) || !FindTable(tableName) || dataSet.Tables[tableName].Rows.Count == 0)
                return null;
            else
            {
                if (!dataSet.Tables[tableName].Columns.Contains(idColName))
                    return null;
                else if (dataSet.Tables[tableName].Select().Any<DataRow>(r => !(r[idColName] is DBNull) && r[idColName].Equals(id)))
                    return dataSet.Tables[tableName].Select().First<DataRow>(r => !(r[idColName] is DBNull) && r[idColName].Equals(id));
                else
                    return null;
            }
        }
        /// <summary>
        /// executes an stored procedure and fills the dataset with the result
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="cnn"></param>
        /// <param name="sprocName"></param>
        /// <param name="paramList"></param>
        /// <returns></returns>
        internal DataSet Exceute<T>(string cnn, object repository,string methodName, object[] paramList)
        {
            try
            {
                allTables = ReflectionHelper.FindAllTables<T>(string.Empty,context);
                SPInfo info= ReflectionHelper.GetSPInfo(repository,methodName);
                mainTableName = ReflectionHelper.TableName<T>(string.Empty);
                DataSet res = new DataSet();
                using (SqlConnection connection = new SqlConnection(cnn))
                {
                    //Create connection to the DB
                    using (SqlCommand searchCommand = new SqlCommand())
                    {
                        searchCommand.CommandType = CommandType.StoredProcedure;
                        searchCommand.CommandText = info.DBName;
                        searchCommand.Connection = connection;
                        if(paramList!=null){
                            for(int i=0;i<paramList.Count<object>();i++){
                                SqlParameter sqlP = new SqlParameter
                                {
                                    SqlDbType = info.Params[i].ParameterType,
                                    ParameterName = info.Params[i].DBName,
                                    Value = paramList[i]
                                };
                                searchCommand.Parameters.Add(sqlP);
                            }
                        }
                        //Create Data Adapter
                        using (SqlDataAdapter dataAdapter = new SqlDataAdapter(searchCommand))
                        {
                            //Fill the data Table with the result
                            DataTable dt = new DataTable(mainTableName);
                            dataAdapter.Fill(dt);
                            foreach (DataColumn col in dt.Columns)
                                col.ColumnName = mainTableName + "_" + col.ColumnName;
                            res.Tables.Add(dt);
                        }
                    }
                }
                return res;
            }
            catch
            {
                //TODO: throw proper exceptions
                throw;
            }
        }
        internal T ExceuteScalar<T>(string cnn, object repository, string methodName, object[] paramList)
        {
            try
            {
                bool hasOutputParam = false;
                SPInfo info = ReflectionHelper.GetSPInfo(repository, methodName);
                mainTableName = ReflectionHelper.TableName<T>(string.Empty);
                DataSet res = new DataSet();
                T scalarResult;
                SqlParameter outParam=null;
                using (SqlConnection connection = new SqlConnection(cnn))
                {
                    //Create connection to the DB
                    using (SqlCommand searchCommand = new SqlCommand())
                    {
                        searchCommand.CommandType = CommandType.StoredProcedure;
                        searchCommand.CommandText = info.DBName;
                        searchCommand.Connection = connection;
                        if (paramList != null)
                        {
                            for (int i = 0; i < paramList.Count<object>(); i++)
                            {
                                SqlParameter sqlP = new SqlParameter
                                {
                                    SqlDbType = info.Params[i].ParameterType,
                                    ParameterName = info.Params[i].DBName,
                                    Value = paramList[i],
                                    Direction=info.Params[i].IsOutput? ParameterDirection.Output:ParameterDirection.Input
                                };
                                searchCommand.Parameters.Add(sqlP);
                                if (info.Params[i].IsOutput){
                                    hasOutputParam = true;
                                    outParam=sqlP;
                                }
                            }
                        }
                        //Create Data Adapter
                        using (SqlDataAdapter dataAdapter = new SqlDataAdapter(searchCommand))
                        {
                            //Fill the data Table with the result
                            DataTable dt = new DataTable(mainTableName);
                            dataAdapter.Fill(dt);
                            res.Tables.Add(dt);
                            if (!hasOutputParam)
                                scalarResult = (T)dt.Rows[0][0];
                            else
                                scalarResult =(T) outParam.Value;
                        }
                    }
                }
                return scalarResult;
            }
            catch
            {
                //TODO: throw proper exceptions
                throw;
            }
        }


        internal void AddMemberToContext<T>(Expression<Func<T, object>> member)
        {
            string memberName=new LinqParser().GetMemberName(member);
            if(!context.ContainsKey(typeof(T)))
                context.Add(typeof(T),new HashSet<string>());
            if (!context[typeof(T)].Contains(memberName))
                context[typeof(T)].Add(memberName);

        }
    }
}
